import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

# 参数设置
CAP_NON, CAP_STD = 1000, 800
COST_OWN, COST_OUT = 500, 800
DELTAS = [-0.2, -0.1, 0, 0.1, 0.2]


def preprocess_data():
    try:
        plan = pd.read_excel('./processedData/结果表4.xlsx')
        pred = pd.read_excel('./processedData/结果表1_预测结果.xlsx')

        # 清洗和合并数据
        pred['线路编码'] = pred['线路编码'].str.strip()
        plan['code'] = plan['code'].str.strip()
        plan['是否使用容器'] = plan['cont'].map({'是': 1, '否': 0})

        plan = pd.merge(plan, pred[['线路编码', '日期', '货量']],
                        left_on='code', right_on='线路编码', how='left')
        plan.rename(columns={'货量': '原预测货量'}, inplace=True)

        # 计算容量和装载率
        plan['单车容量'] = np.where(plan['是否使用容器'] == 1, CAP_STD, CAP_NON)
        plan['装载率'] = plan['原预测货量'] / plan['单车容量']
        return plan, plan['装载率'].mean()
    except Exception as e:
        print(f"数据预处理出错: {e}")
        return None, None


def calc_origin_stats(plan, avg_load):
    vehicle_type = plan['veh'].str[:2]
    return {
        'own_count': (vehicle_type == '自有').sum(),
        'out_count': (vehicle_type == '外部').sum(),
        'total_cost': ((vehicle_type == '自有') * COST_OWN + (vehicle_type == '外部') * COST_OUT).sum(),
        'avg_load': avg_load
    }


def simulate_robustness(plan, delta, origin_stats):
    sim = plan.copy()
    sim['扰动后货量'] = sim['原预测货量'] * (1 + delta)
    sim['是否失败'] = sim['扰动后货量'] > sim['单车容量']
    sim['车类型'] = sim['veh'].str[:2]
    sim['成本'] = sim['车类型'].map({'自有': COST_OWN, '外部': COST_OUT})
    sim['扰动装载率'] = np.minimum(sim['扰动后货量'], sim['单车容量']) / sim['单车容量']

    total = len(sim)
    fail_count = sim['是否失败'].sum()
    own_count = (sim['车类型'] == '自有').sum()
    out_count = (sim['车类型'] == '外部').sum()
    total_cost = sim['成本'].sum()
    avg_load = sim['扰动装载率'].mean()

    r1 = fail_count / total
    r2 = (own_count - origin_stats['own_count']) / origin_stats['own_count']
    r3 = (out_count - origin_stats['out_count']) / origin_stats['out_count']
    r4 = avg_load - origin_stats['avg_load']
    r5 = (total_cost - origin_stats['total_cost']) / origin_stats['total_cost']

    return {
        '扰动系数': f"{int(delta * 100)}%",
        '任务失败率 R1': round(r1, 4),
        '自有车使用变化率 R2': round(r2, 4),
        '外部车调用变化率 R3': round(r3, 4),
        '平均装载率变化 R4': round(r4, 4),
        '总成本变化率 R5': round(r5, 4)
    }


def main():
    plan, avg_load = preprocess_data()
    if plan is None:
        return

    origin_stats = calc_origin_stats(plan, avg_load)
    results = [simulate_robustness(plan, delta, origin_stats) for delta in DELTAS]
    result_df = pd.DataFrame(results)

    result_df.to_excel('./processedData/问题四_鲁棒性指标.xlsx', index=False)
    print("问题四评估完成，结果已保存到：问题四_鲁棒性指标.xlsx")
    print(result_df)

    plt.rcParams['font.sans-serif'] = ['Microsoft YaHei']
    plt.rcParams['axes.unicode_minus'] = False
    plt.figure(figsize=(10, 6))
    for col in result_df.columns[1:]:
        plt.plot(result_df['扰动系数'], result_df[col], label=col, marker='o')
    plt.xlabel('扰动系数')
    plt.ylabel('指标变化值')
    plt.title('调度方案鲁棒性评估指标趋势')
    plt.legend()
    plt.grid(True)
    plt.tight_layout()
    plt.savefig('./processedData/问题四_鲁棒性趋势图.jpg', dpi=500)
    print("趋势图已生成：问题四_鲁棒性趋势图.jpg")


if __name__ == "__main__":
    main()
